﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Linq;
using System.Text;
using DTO;

namespace DAO
{
    public class TraCuuChuyenBay_DAO
    {
        public int SoGheTrong(string maChuyenBay)
        {
            int SL = 0;
            QLCBDataContext context = new QLCBDataContext();
            var query = (from tt in context.TinhTrangVes
                         where tt.MaChuyenBay == maChuyenBay
                         select tt.SoGheTrong).Sum();
            SL = (int)query;
            return SL;
        }
        public int SoGheDat(string maChuyenBay)
        {
            int SL = 0;
            QLCBDataContext context = new QLCBDataContext();
            var query = (from tt in context.TinhTrangVes
                         where tt.MaChuyenBay == maChuyenBay
                         select tt.SoGheDat).Sum();
            SL = (int)query;
            return SL;
        }

        public BindingList<TraCuuChuyenBay_DTO> GetChuyenBayTheoTuyenBayList(string SanBayDi, string SanBayDen)
        {
            BindingList<TraCuuChuyenBay_DTO> listChuyenBay = new BindingList<TraCuuChuyenBay_DTO>();
            try
            {
                QLCBDataContext context = new QLCBDataContext();
                var query = from cb in context.ChuyenBays
                            join tb in context.TuyenBays on cb.MaTuyenBay equals tb.MaTuyenBay
                            where tb.SanBayDi == SanBayDi && tb.SanBayDen == SanBayDen
                            select new
                            {
                                cb.MaChuyenBay,
                                tb.MaTuyenBay,
                                tb.SanBayDi,
                                tb.SanBayDen,
                                cb.ThoiGianBay,
                                cb.Ngay_Gio,
                            };
                int mSTT = 1;
                foreach (var nl in query)
                {
                    TraCuuChuyenBay_DTO DTO = new TraCuuChuyenBay_DTO();
                    DTO.STT = mSTT;
                    DTO.ChuyenBay = nl.MaChuyenBay;
                    DTO.SanBayDi = nl.SanBayDi;
                    DTO.SanBayDen = nl.SanBayDen;
                    DTO.ThoiGian = (int)nl.ThoiGianBay;
                    DTO.KhoiHanh = (DateTime)nl.Ngay_Gio;
                    DTO.SoGheTrong = SoGheTrong(DTO.ChuyenBay);
                    DTO.SoGheDat = SoGheDat(DTO.ChuyenBay);
                    listChuyenBay.Add(DTO);
                    mSTT++;
                }
            }
            catch (System.Exception ex)
            {
                string str = ex.Message;
            }
            return listChuyenBay;
        }
        public BindingList<TraCuuChuyenBay_DTO> GetChuyenBayTheoNgayBayList(DateTime Tu, DateTime Den)
        {
            BindingList<TraCuuChuyenBay_DTO> listChuyenBay = new BindingList<TraCuuChuyenBay_DTO>();
            try
            {
                QLCBDataContext context = new QLCBDataContext();
                var query = from cb in context.ChuyenBays
                            join tb in context.TuyenBays on cb.MaTuyenBay equals tb.MaTuyenBay
                            select new
                            {
                                cb.MaChuyenBay,
                                tb.MaTuyenBay,
                                tb.SanBayDi,
                                tb.SanBayDen,
                                cb.ThoiGianBay,
                                cb.Ngay_Gio,
                            };
                int mSTT = 1;
                foreach (var nl in query.Where(i => i.Ngay_Gio >= Tu && i.Ngay_Gio <= Den))
                {
                    TraCuuChuyenBay_DTO DTO = new TraCuuChuyenBay_DTO();
                    DTO.STT = mSTT;
                    DTO.ChuyenBay = nl.MaChuyenBay;
                    DTO.SanBayDi = nl.SanBayDi;
                    DTO.SanBayDen = nl.SanBayDen;
                    DTO.ThoiGian = (int)nl.ThoiGianBay;
                    DTO.KhoiHanh = (DateTime)nl.Ngay_Gio;
                    DTO.SoGheTrong = SoGheTrong(DTO.ChuyenBay);
                    DTO.SoGheDat = SoGheDat(DTO.ChuyenBay);
                    listChuyenBay.Add(DTO);
                    mSTT++;
                }
            }
            catch (System.Exception ex)
            {
                string str = ex.Message;
            }
            return listChuyenBay;
        }
        public BindingList<TraCuuChuyenBay_DTO> GetChuyenBayList()
        {
            BindingList<TraCuuChuyenBay_DTO> listChuyenBay = new BindingList<TraCuuChuyenBay_DTO>();
            try
            {
                QLCBDataContext context = new QLCBDataContext();
                var query = from cb in context.ChuyenBays
                            join tb in context.TuyenBays on cb.MaTuyenBay equals tb.MaTuyenBay
                            select new
                            {
                                cb.MaChuyenBay,
                                tb.MaTuyenBay,
                                tb.SanBayDi,
                                tb.SanBayDen,
                                cb.ThoiGianBay,
                                cb.Ngay_Gio,
                            };
                int mSTT = 1;
                foreach (var nl in query)
                {
                    TraCuuChuyenBay_DTO DTO = new TraCuuChuyenBay_DTO();
                    DTO.STT = mSTT;
                    DTO.ChuyenBay = nl.MaChuyenBay;
                    DTO.SanBayDi = nl.SanBayDi;
                    DTO.SanBayDen = nl.SanBayDen;
                    DTO.ThoiGian = (int)nl.ThoiGianBay;
                    DTO.KhoiHanh = (DateTime)nl.Ngay_Gio;
                    DTO.SoGheTrong = SoGheTrong(DTO.ChuyenBay);
                    DTO.SoGheDat = SoGheDat(DTO.ChuyenBay);
                    listChuyenBay.Add(DTO);
                    mSTT++;
                }
            }
            catch (System.Exception ex)
            {
                string str = ex.Message;
            }
            return listChuyenBay;
        }
        public BindingList<TraCuuChuyenBay_DTO> GetChuyenBayTheoSoGheTrongList(int Tu, int Den)
        {
            BindingList<TraCuuChuyenBay_DTO> list = GetChuyenBayList();
            BindingList<TraCuuChuyenBay_DTO> listChuyenBay = new BindingList<TraCuuChuyenBay_DTO>();
            try
            {
                int mSTT = 1;
                foreach (var nl in list.Where(i => i.SoGheTrong >= Tu && i.SoGheTrong <= Den))
                {
                    TraCuuChuyenBay_DTO DTO = new TraCuuChuyenBay_DTO();
                    DTO.STT = mSTT;
                    DTO.ChuyenBay = nl.ChuyenBay;
                    DTO.SanBayDi = nl.SanBayDi;
                    DTO.SanBayDen = nl.SanBayDen;
                    DTO.ThoiGian = (int)nl.ThoiGian;
                    DTO.KhoiHanh = (DateTime)nl.KhoiHanh;
                    DTO.SoGheTrong = nl.SoGheTrong;
                    DTO.SoGheDat = nl.SoGheDat;
                    listChuyenBay.Add(DTO);
                    mSTT++;
                }
            }
            catch (System.Exception ex)
            {
                string str = ex.Message;
            }
            return listChuyenBay;
        }
    }
}
